Import Alias Part Numbers

Task/Problem Overview

Currently, there is no built-in mechanism within the software’s import engine to import alias part numbers. Therefore, it must be completed manually using SQL Management Studio and some SQL scripts. This task should only be completed by more advanced users, such as system administrators. If done incorrectly, data problems can result.

Create ImportInventoryAliases Table

• First, check if the ImportInventoryAliases table exists within SQL Management Studio.

• If this procedure has already been followed on your system, then the table should already exist. Otherwise, copy and paste the script below into SQL Management Studio and run it to create the table for the first time. The table contains columns for UPC, MFG, PartNumber, & AliasText.

CREATE TABLE dbo.ImportInventoryAliases

(

UPC varchar(MAX) NULL,

MFG varchar(MAX) NULL,

PartNumber varchar(MAX) NULL,

AliasText varchar(MAX) NULL

)

Create Spreadsheet

• You now will need to create a Microsoft Excel file with the same headers as the table that was just created, which are UPC, MFG, PartNumber, & AliasText. For part matching in your system, you can choose to only put in a UPC number, or you can choose to put in a MFG and PartNumber combination. The AliasText should be what you want the alias part number to be, which is what we are importing. Save this file somewhere on the SQL Server.

• Before saving the spreadsheet, we need to make sure all data within is in the text format. To do so, first highlight everything by clicking the cell between A & 1.

• Next, right-click anywhere on the sheet and choose the Format Cells option.

• Choose the Text option and hit ok. Now you can save the spreadsheet into the Excel format. It is now ready for the import.

Import Spreadsheet Into Table

• Right-click on the database name and navigate to Tasks -> Import Data.

• Change the data source to Microsoft Excel and navigate to the file path on the server.

• Change the destination to SQL Server Native Client. Use SQL Server authentication, and enter your database credentials.

• Keep the default option to copy data from one or more tables or views.

• Change the destination to the ImportInventoryAlias table, which is where we are going to put the data. You shouldn’t need to edit the mappings as long as the column names in the spreadsheet match the column names in the table exactly.

• If you followed all the instructions carefully, you shouldn’t have any errors on the next screen and will be allowed to proceed.

• Go ahead and hit the Finish button to complete the importation of the spreadsheet data into the database table.

Verify The Data

• You can verify the data was imported into the table correctly by navigating to it, right-clicking, and choosing the Select Top 1000 Rows option.

Run The Import SQL Script

• Run the following import script within SQL Management Studio to commit the alias part numbers into the database. Copy, paste, and run.

DECLARE @AliasText VARCHAR(MAX)

DECLARE @UPC VARCHAR(MAX)

DECLARE @PartNumber VARCHAR(MAX)

DECLARE @MFG VARCHAR(MAX)

DECLARE @InventorySys INT

DECLARE AliasImportCursor CURSOR FOR

SELECT * FROM ImportInventoryAliases

OPEN AliasImportCursor

FETCH NEXT FROM AliasImportCursor

INTO @UPC, @MFG, @PartNumber, @AliasText

WHILE @@FETCH_STATUS = 0

BEGIN

SET @InventorySys = 0

SELECT @InventorySys = InventorySys FROM Inventory WHERE UPC = @UPC AND CurrentValue = 1

IF ISNULL(@InventorySys, 0) = 0

SELECT @InventorySys = InventorySys FROM Inventory

LEFT OUTER JOIN Manufacturers ON Manufacturers.ManufacturerSys = Inventory.ManufacturerSys

WHERE PartNumber = @PartNumber AND Inventory.CurrentValue = 1 AND Manufacturers.ManufacturerShortName = @MFG

--Make sure the inventory item is valid

IF ISNULL(@InventorySys,0) <> 0

BEGIN

--Make sure that it's not already in the aliases table and that the text is not empty

IF NOT EXISTS(select * from aliases where inventorysys = @InventorySys and aliaspartnumber = @AliasText AND CurrentValue = 1) AND ISNULL(@AliasText,'') <> ''

INSERT INTO aliases (inventorysys, aliaspartnumber, AliasPartNumberSearch, currentvalue)

values (@InventorySys,@AliasText,dbo.os_CreatePartNumberSearch(@AliasText), 1)

END

FETCH NEXT FROM AliasImportCursor

INTO @UPC, @MFG, @PartNumber, @AliasText

END

CLOSE AliasImportCursor;

DEALLOCATE AliasImportCursor;

Spot Check A Part Number

• Go to the item manager and spot check a part number from your spreadsheet to make sure it made it into the alias part numbers section. This is how you will know that the import was successful.

Empty The ImportInventoryAliases Table

• Now that the import is complete, we need to empty the ImportInventoryAliases table so that it will be ready for the next time you perform this operation. Simply run the following SQL script in SQL Management Studio.

TRUNCATE TABLE ImportInventoryAliases

• That’s all there is to it. If you have any questions or run into any problems, please contact Savance for further assistance.